
libname cadata '.';
libname seddwork '.';

PROC IMPORT OUT= WORK.CCI
            DATAFILE= "cci2012.csv"
            DBMS=CSV REPLACE;
     GETNAMES=YES;
     DATAROW=2;
RUN;

PROC IMPORT OUT= WORK.CaMedIncome 
            DATAFILE= "MedianIncome20072011ACS.csv" 
            DBMS=CSV REPLACE;
     GETNAMES=Yes;
     DATAROW=2; 
RUN;

%macro sedd(year=);

data seddwork.ca_sedd_&year (rename=(dx_prin=dx1 odx1=dx2 
odx2=dx3 odx3=dx4 odx4=dx5 odx5=dx6 odx6=dx7 odx7=dx8
odx8=dx9 odx9=dx10 odx10=dx11 odx11=dx12 odx12=dx13
odx13=dx14 odx14=dx15 brthdate=bdate));
set cadata.wherry_ed&year (rename=(race=oldrace));
FIPS=6;
Year=&year;
*LW: create unique observation number;
key=_N_;
*LW: create hospid var though we do not have this info;
hospid=.;
*LW: since no discharge quarter use admit info;
admtmonth=month(admtdate);
dqtr=.;
if (admtmonth<=3) then dqtr=1;
if (4<=admtmonth<=6) then dqtr=2;
if (7<=admtmonth<=9) then dqtr=3;
if (10<=admtmonth<=12) then dqtr=4;
*LW: create age variable;
days=serv_dt-brthdate;
age=floor(days/365);
*LW: create birth month and year vars;
bmonth=month(brthdate);
byear=year(brthdate);
*LW: destring race variable;
race=input(oldrace,7.);
count=1;

ThreeDigDx2=substr(trim(left(dx_prin)),1,1);
if ThreeDigDx2="E" then ecode=1;
else ecode=0;
if ThreeDigDx2="V" then ThreeDigDx=substr(odX1,1,3)*1;
else ThreeDigDx=substr(dx_prin,1,3)*1; 

if ThreeDigDx LE 679 and ThreeDigDx GE 630 then preg=1;
else preg=0;
if threeDigDx LE 319 and ThreeDigDx GE 290 then mental=1;
else mental=0;
if threeDigDX GE 800 and threeDigDX LT 830 then fracture=1;
else fracture=0;
if threeDigDX GE 800 and threeDigDX LT 960 then injury=1;
else injury=0;
if threeDigDX=. then fracture=.;
if threeDigDX=. then mental=.;
if threeDigDX=. then injury=.;

if threeDigDX GE 540 and threeDigDX LT 544 then Appendicitis=1;
else Appendicitis=0;

if threedigdx GT 0 and threedigdx LE 139 then infect=1;
else infect=0;
if threedigdx GT 139 and threedigdx LE 239 then neoplasms=1;
else neoplasms=0;
if threedigdx GT 239 and threedigdx LE 279 then endometa=1;
else endometa=0;
if threedigdx GT 279 and threedigdx LE 289 then blood=1;
else blood=0;
if threedigdx GT 289 and threedigdx LE 319 then mentaldisorders=1;
else mentaldisorders=0;
if threedigdx GT 319 and threedigdx LE 359 then nervoussys=1;
else nervoussys=0;
if threedigdx GT 359 and threedigdx LE 389 then sense=1;
else sense=0;
if threedigdx GT 389 and threedigdx LE 459 then circulatory=1;
else circulatory=0;
if threedigdx GT 459 and threedigdx LE 519 then respiratory=1;
else respiratory=0;
if threedigdx GT 519 and threedigdx LE 579 then digestive=1;
else digestive=0;
if threedigdx GT 579 and threedigdx LE 629 then genitourinary=1;
else genitourinary=0;
if threedigdx GT 629 and threedigdx LE 679 then pregchild=1;
else pregchild=0;
if threedigdx GT 679 and threedigdx LE 709 then skin=1;
else skin=0;
if threedigdx GT 709 and threedigdx LE 739 then muscle=1;
else muscle=0;
if threedigdx GT 739 and threedigdx LE 759 then congenital=1;
else congenital=0;
if threedigdx GT 759 and threedigdx LE 779 then perinatal=1;
else perinatal=0;
if threedigdx GT 779 and threedigdx LE 799 then illdefined=1;
else illdefined=0;
if threedigdx GT 799 and threedigdx LE 999 then injpois=1;
else injpois=0;
if preg=1 then delete;
if oldrace="R3" then black=1;
else black=0;

if payer="MA" then public=1;
else public=0;
if payer="MB" then public=1;
if payer="MC" then public=1;
if payer="09" then self=1;
else self=0;
if payer="HM" then private=1;
else private=0;
if payer="12" then private=1;
if payer="14" then private=1;
if payer="BL" then private=1;
if payer="CI" then private=1;
run;

data cci;
set cci;
DX1=ICD_9_CM_CODE;
run;


proc sort data=seddwork.ca_sedd_&year._core;
by DX1;
run;

proc sort data=cci;
by DX1;
run;

data seddwork.ca_sedd_&year._core;
merge seddwork.ca_sedd_&year._core cci;
by DX1;
if count=. then delete;
Chronic=CATEGORY_DESCRIPTION*1;
run; 
%mend;

%sedd(year=2009)

data seddwork.ca_sedd_2009_core (drop=zipinc_qrtl);
set seddwork.ca_sedd_2009_core;
patzip2=patzip*1;
run;

data camedincome (drop=GEO_id GEO_display_label Estimate MoE);
set camedincome;
patzip2=zipcode;
MedianIncome=Estimate;
if patzip2=. then delete;
run;

proc sort data=camedincome;
by patzip2;
run;

proc sort data=seddwork.ca_sedd_2009_core;
by patzip2;
run;

data seddwork.ca_sedd_2009_core ;
merge seddwork.ca_sedd_2009_core camedincome;
by patzip2;
if MedianIncome < 39999 then zipinc_qrtl=1;
else zipinc_qrtl=2;
if MedianIncome = . then zipinc_qrtl=.;
if count=. then delete;
run;

proc sort data=seddwork.ca_sedd_2009_core;
by ThreeDigDx;
quit;

data hcup.charges;
set hcup.charges;
if ThreeDigDx=. then delete;
run;

proc sort data=hcup.charges;
by ThreeDigDx;
quit;


data seddwork.ca_sedd_2009_core ;
merge seddwork.ca_sedd_2009_core hcup.charges;
by ThreeDigDx;
run;


proc sql;
create table seddwork.CaliED2009 as select 

sum(public) as public,
sum(private) as private,
sum(self) as self,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(Chronic) as HCUPChronic,
sum(Appendicitis) as Appendicitis,
sum(fracture) as fracture,
sum(injury) as injury,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.ca_sedd_2009_core
group by bmonth, byear;
quit;

proc sql;
create table seddwork.CaliED2009 as select unique * from seddwork.CaliED2009;
quit;

data seddwork.CaliED2009;
set seddwork.CaliED2009;
FIPS=6;
run;


proc sql;
create table seddwork.NotBlackCaliED2009 as select 
sum(public) as public,
sum(private) as private,
sum(self) as self,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,

sum(Chronic) as HCUPChronic,
sum(Appendicitis) as Appendicitis,
sum(fracture) as fracture,
sum(injury) as injury,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.ca_sedd_2009_core
where black=0
group by bmonth, byear;
quit;

proc sql;
create table seddwork.NotBlackCaliED2009 as select unique * from seddwork.NotBlackCaliED2009;
quit;


data seddwork.NotBlackCaliED2009;
set seddwork.NotBlackCaliED2009;
FIPS=6;
run;

PROC EXPORT DATA= seddwork.NotBlackCaliED2009 
            OUTFILE="NotBlackCaliED2009.csv" 
            DBMS=CSV REPLACE;
     PUTNAMES=YES;
RUN;




proc sql;
create table seddwork.BlackCaliED2009 as select 

sum(public) as public,
sum(private) as private,
sum(self) as self,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Chronic) as HCUPChronic,
sum(Appendicitis) as Appendicitis,
sum(fracture) as fracture,
sum(injury) as injury,
sum(count) as count,
sum(cost) as cost,
sum(TOTCHG) as charges,
bmonth as bmonth,
byear as byear
from seddwork.ca_sedd_2009_core
where black=1
group by bmonth, byear;
quit;

proc sql;
create table seddwork.BlackCaliED2009 as select unique * from seddwork.BlackCaliED2009;
quit;

data seddwork.BlackCaliED2009;
set seddwork.BlackCaliED2009;
FIPS=6;
run;

proc sql;
create table seddwork.LICaliED2009 as select 
sum(public) as public,
sum(private) as private,
sum(self) as self,


sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Chronic) as HCUPChronic,
sum(Appendicitis) as Appendicitis,
sum(fracture) as fracture,
sum(injury) as injury,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.ca_sedd_2009_core
where ZIPINC_QRTL LT 2
group by bmonth, byear;
quit;

*where ZIPINC_QRTL LT 2;
proc sql;
create table seddwork.LICaliED2009 as select unique * from seddwork.LICaliED2009;
quit;


proc sql;
create table seddwork.BlackLICaliED2009 as select 

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Chronic) as HCUPChronic,
sum(Appendicitis) as Appendicitis,
sum(fracture) as fracture,
sum(injury) as injury,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.ca_sedd_2009_core
where ZIPINC_QRTL LT 2 and black=1
group by bmonth, byear;
quit;

proc sql;
create table seddwork.BlackLICaliED2009 as select unique * from seddwork.BlackLICaliED2009;
quit;


proc sql;
create table seddwork.NotBlackLICaliED2009 as select 
sum(public) as public,
sum(private) as private,
sum(self) as self,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,

sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Chronic) as HCUPChronic,
sum(Appendicitis) as Appendicitis,
sum(fracture) as fracture,
sum(injury) as injury,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.ca_sedd_2009_core
where ZIPINC_QRTL LT 2 and black=0
group by bmonth, byear;
quit;

proc sql;
create table seddwork.NotBlackLICaliED2009 as select unique * from seddwork.NotBlackLICaliED2009;
quit;
